using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;
using System.Data;
using System.Collections;


namespace DAOLayer
{
    public class CuonSachDao
    {
        public void ThemCuonSach(CuonSachDto cs)
        {
            OleDbConnection cn = Database.ConnectionData();
            string strSQL = "Insert into CuonSach (Isbn,MaCuonSach,NgayNhan,NguoiNhan,TinhTrang) values(?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Isbn", OleDbType.Integer);
            cmd.Parameters["@Isbn"].Value = cs.Isbn;

            cmd.Parameters.Add("@MaCuonSach", OleDbType.WChar);
            cmd.Parameters["@MaCuonSach"].Value = cs.MaCuonSach;

            cmd.Parameters.Add("@NgayNhan", OleDbType.Date);
            cmd.Parameters["@NgayNhan"].Value = cs.NgayNhan;

            cmd.Parameters.Add("@NguoiNhan", OleDbType.WChar);
            cmd.Parameters["@NguoiNhan"].Value = cs.NguoiNhan;

            cmd.Parameters.Add("@TinhTrang", OleDbType.Integer);
            cmd.Parameters["@TinhTrang"].Value = cs.TinhTrang;

            cmd.ExecuteNonQuery();
            strSQL = "Select @@IDENTITY";
            cmd = new OleDbCommand(strSQL, cn);
            cs.Isbn = (int)cmd.ExecuteScalar();
            cn.Close();
        }

        public void CapNhatBangCuonSach(DataTable dt)
        {
            OleDbConnection cn;
            cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Select * From CuonSach";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            da.Update(dt);
            cn.Close();
        }


        public void CapNhatCuonSach(string ma,int tt)
        {

            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Update CuonSach Set TinhTrang = ? where MaCuonSach = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaCuonsach", OleDbType.WChar);
            cmd.Parameters.Add("@TinhTrang", OleDbType.Integer);

            cmd.Parameters["@MaCuonsach"].Value = ma;
           cmd.Parameters["@TinhTrang"].Value = tt;
            
            cmd.ExecuteNonQuery();
            cn.Close();

        }

        
        public ArrayList TimKiem(string ma)
        {
            ArrayList ds = new ArrayList();
            CuonSachDto csDto = null;
            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Select * From CuonSach Where MaCuonSach = ?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaCuonSach", OleDbType.WChar);
            cmd.Parameters["@MaCuonSach"].Value = ma;
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                csDto = new CuonSachDto();
                csDto.Isbn = Int32.Parse(dr["Isbn"].ToString());
                csDto.MaCuonSach = dr["MaCuonSach"].ToString();
                csDto.NgayNhan = DateTime.Parse(dr["NgayNhan"].ToString());
                csDto.NguoiNhan = dr["NguoiNhan"].ToString();
                csDto.TinhTrang = Int32.Parse(dr["TinhTrang"].ToString());
                ds.Add(csDto);
            }

            dr.Close();
            cn.Close();
            return ds;
        }

        public DataTable LayBangCuonSach()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn = Database.ConnectionData();
            string strSQL = "Select * From CuonSach";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public DataTable LayBangCSTheoTieuChuan(CuonSachCtr csCrt)
        {
            OleDbConnection cn = Database.ConnectionData();
            DataTable dt = new DataTable();
            OleDbCommand cmd = Query(csCrt, cn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        private OleDbCommand Query(CuonSachCtr cs, OleDbConnection cn)
        {
            OleDbCommand cmd = new OleDbCommand();
            string strDKMa = " 1=1 ";
            string strDKTen = " 1=1 ";
            string strDKLoai = " 1=1 ";
            string strDKTacGia = " 1=1 ";

            if (cs.MaCuonSach !="")
            {
                strDKMa = " MaCuonSach like ? ";
                cmd.Parameters.Add("@MaCuonSach", OleDbType.WChar);
                cmd.Parameters["@MaCuonSach"].Value = "%" + cs.MaCuonSach + "%";
            }
            if (cs.TenSach != "")
            {
                strDKTen = " TuaSach like ? ";
                cmd.Parameters.Add("@TuaSach", OleDbType.VarWChar);
                cmd.Parameters["@TuaSach"].Value = "%" + cs.TenSach + "%";
            }
            if (cs.LoaiSach != 0)
            {
                strDKLoai = " TheLoai = ? ";
                cmd.Parameters.Add("@TheLoai", OleDbType.Integer);
                cmd.Parameters["@TheLoai"].Value = cs.LoaiSach;
            }

            if (cs.TacGia != "")
            {
                strDKTacGia = " TacGia like ? ";
                cmd.Parameters.Add("@TacGia", OleDbType.VarWChar);
                cmd.Parameters["@TacGia"].Value = "%" + cs.TacGia + "%";
            }
            string strDKWhere = " Where  ";
            strDKWhere += strDKMa;
            strDKWhere += " and " + strDKTen;
            strDKWhere += " and " + strDKLoai;
            strDKWhere += " and " + strDKTacGia;

            string strSQL = "select DISTINCT cs.Isbn, cs.MaCuonSach,ds.TuaSach,ds.TheLoai,cs.NguoiNhan,cs.NgayNhan,cs.TinhTrang from CuonSach cs,DauSach ds ";
            strSQL += strDKWhere;
            strSQL += " and " + "cs.Isbn = ds.Isbn";
            //strSQL += " Order by cs.NguoiNhan ";

            cmd.Connection = cn;
            cmd.CommandText = strSQL;
            return cmd;
        }

        public CuonSachDto LayCuonSachTheoMa(string ma)
        {
            CuonSachDto csdto = new CuonSachDto();
            OleDbConnection cn = Database.ConnectionData();
            string strSQL = "select * from CuonSach where MaCuonSach = ? ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@MaCuonSach", OleDbType.WChar);
            cmd.Parameters["@MaCuonSach"].Value = ma;

            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            csdto.Isbn=0;
            while (dr.Read())
            {
                csdto.Isbn = int.Parse(dr["Isbn"].ToString());
                csdto.MaCuonSach = dr["MaCuonSach"].ToString();
                csdto.NgayNhan = (DateTime)dr["NgayNhan"];
                csdto.NguoiNhan = dr["NguoiNhan"].ToString();
                csdto.TinhTrang = int.Parse(dr["TinhTrang"].ToString());
            }
            cn.Close();
            return csdto;
        }

        public ArrayList LayDanhSachCuonSach()
        {
            ArrayList ds = new ArrayList();
            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Select * From CuonSach";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                CuonSachDto csDto = new CuonSachDto();
                csDto.Isbn = (int)dr["Isbn"];
                csDto.MaCuonSach = (string)dr["MaCuonSach"];
                csDto.NgayNhan = (DateTime)dr["NgayNhan"];
                csDto.NguoiNhan = (string)dr["NguoiNhan"];
                csDto.TinhTrang = Int32.Parse(dr["TinhTrang"].ToString());
                ds.Add(csDto);
            }

            dr.Close();
            cn.Close();
            return ds;
        }

       

    }
}